﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace ZhuXingFamilys.ExportExcel.utils
{   
    /**
     * excel转换实体类工具
     */
    public class ExcelConvert
    {
        public static ObservableCollection<T> ToEntityStreamListForExcelData<T>(Stream stream, string fileName, bool firstRow2Name) where T : new()
        {
            return ToEntityListForExcelData<T>(stream, fileName, firstRow2Name);
        }

		public static List<T> ToEntityStreamList<T>(Stream stream, string fileName, bool firstRow2Name) where T : new()
		{
			return ToEntityList<T>(stream, fileName, 0, firstRow2Name);
		}
		public static List<T> ToEntityStreamList<T>(Stream stream, string fileName,int index, bool firstRow2Name) where T : new()
		{
			return ToEntityList<T>(stream, fileName, index, firstRow2Name);
		}

		private static string schremaName = string.Empty;
        public static string GetEntityName() 
        {
            return schremaName;
        }

		/// <summary>
		/// Excel转换为实体列表ForExcelData
		private static List<T> ToEntityList<T>(Stream stream, string fileName, int SheetIndex, bool firstRow2Name)
			where T : new()
		{
			// 加载Excel
			IWorkbook workbook;
			try
			{
				// 格式判断
				if (fileName.EndsWith(".xlsx"))
				{
					workbook = new XSSFWorkbook(stream);
				}
				else
				{
					workbook = new HSSFWorkbook(stream);
				}

				// 返回实体集
				var entities = new List<T>();

				// 读取第一页
				ISheet sheet = workbook.GetSheetAt(SheetIndex);

				// 遍历所有行
				IEnumerator rows = sheet.GetRowEnumerator();

				// 反射实体判断实体行
				var columns = new List<EntityColumn>();

				#region 反射实体判断实体行对应Excel文件列
				// 名称行
				var excelFirstRow = new List<string>();
				if (firstRow2Name && rows.MoveNext())
				{
					var row = (IRow)rows.Current;
					for (int i = 0; i <= row.LastCellNum; i++)
					{
						ICell cell = row.GetCell(i);
						if (cell == null) excelFirstRow.Add("");
						else excelFirstRow.Add(cell.ToString());
					}
				}

				// 反射
				PropertyInfo[] propertys = typeof(T).GetProperties();
				int propertyIndex = 0;
				foreach (PropertyInfo property in propertys)
				{
					object[] excelColumns = property.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
					if (excelColumns.Length > 0)
					{
						string like = (excelColumns[0] as ExcelColumnAttribute).DisplayNameLike;
						int excelIndex = (excelColumns[0] as ExcelColumnAttribute).ExcelIndex;
						if (!string.IsNullOrEmpty(like))
						{
							for (int index = 0; index < excelFirstRow.Count; index++)
							{
								if (excelFirstRow[index].Contains(like))
								{
									columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = index });
									break;
								}
							}
						}
						else if (excelIndex > -1)
						{
							columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = excelIndex });
						}
						else
						{
							columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = propertyIndex });
						}
					}
					propertyIndex++;
				}
				#endregion

				// 遍历数据行
				while (rows.MoveNext())
				{
					var row = (IRow)rows.Current;
					var entity = new T();
					PropertyInfo[] items = entity.GetType().GetProperties();
					foreach (PropertyInfo property in items)
					{
						EntityColumn column = columns.FirstOrDefault(t => t.ColumnName == property.Name);
						if (column != null)
						{
							ICell cell = row.GetCell(column.ExcelIndex);
							if (cell == null)
							//if (column.ExcelIndex - row.FirstCellNum >= row.Cells.Count || column.ExcelIndex - row.FirstCellNum < 0)
							{
								continue;
							}

							Object val = cell.ToString();

							#region 数据类型转换
							if (property.PropertyType == typeof(DateTime))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = cell.DateCellValue;
								}
								else
								{
									DateTime d;
									DateTime.TryParse(val.ToString(), out d);
									val = d;
								}
							}
							else if (property.PropertyType == typeof(string))
							{
								val = val.ToString().Trim();
							}
							if (property.PropertyType == typeof(Int32))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = (int)Math.Round(cell.NumericCellValue);
								}
								Int32 d;
								Int32.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(UInt32))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = Math.Round(cell.NumericCellValue);
								}
								UInt32 d;
								UInt32.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(Int16))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = Math.Round(cell.NumericCellValue);
								}
								Int16 d;
								Int16.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(UInt16))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = Math.Round(cell.NumericCellValue);
								}
								UInt16 d;
								UInt16.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(float))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = cell.NumericCellValue;
								}
								float d;
								float.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(decimal))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = cell.NumericCellValue;
								}
								decimal d;
								decimal.TryParse(val.ToString(), out d);
								val = d;
							}
							else if (property.PropertyType == typeof(double))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = cell.NumericCellValue;
								}
								else
								{
									double d;
									double.TryParse(val.ToString(), out d);
									val = d;
								}
							}
							else if (property.PropertyType == typeof(byte))
							{
								if (cell.CellType == CellType.Numeric)
								{
									val = (byte)Convert.ToInt32(cell.NumericCellValue);
								}
								else
								{
									double d;
									double.TryParse(val.ToString(), out d);
									val = (byte)d;
								}
							}
							#endregion

							property.SetValue(entity, val, null);
						}
					}

					entities.Add(entity);
				}

				// 返回结果
				return entities;
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		/// <summary>
		/// Excel转换为实体列表ForExcelData
		private static ObservableCollection<T> ToEntityListForExcelData<T>(Stream stream, string fileName, bool firstRow2Name)
            where T : new()
        {
            // 加载Excel
            IWorkbook workbook;
            try
            {
                // 格式判断
                if (fileName.EndsWith(".xlsx"))
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }

                // 返回实体集
                var entities = new ObservableCollection<T>();

                // 读取第一页
                ISheet sheet = workbook.GetSheetAt(0);

                // 遍历所有行
                IEnumerator rows = sheet.GetRowEnumerator();

                // 反射实体判断实体行
                var columns = new List<EntityColumn>();

                #region 反射实体判断实体行对应Excel文件列
                // 第一行 
                rows.MoveNext();

                // 第二行 工程名称
                if (rows.MoveNext())
                {
                    var row = (IRow)rows.Current;
                    ICell cell = row.GetCell(0);
                    if (cell == null) schremaName = "";
                    else schremaName = cell.ToString().Replace("工程名称：", "");
                }

                // 第三行 标题
                var excelFirstRow = new List<string>();
                if (firstRow2Name && rows.MoveNext())
                {
                    var row = (IRow)rows.Current;
                    for (int i = 0; i <= row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null) excelFirstRow.Add("");
                        else excelFirstRow.Add(cell.ToString());
                    }
                }

                

                // 反射
                PropertyInfo[] propertys = typeof(T).GetProperties();
                int propertyIndex = 0;
                foreach (PropertyInfo property in propertys)
                {
                    object[] excelColumns = property.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                    if (excelColumns.Length > 0)
                    {
                        string like = (excelColumns[0] as ExcelColumnAttribute).DisplayNameLike;
                        int excelIndex = (excelColumns[0] as ExcelColumnAttribute).ExcelIndex;
                        if (!string.IsNullOrEmpty(like))
                        {
                            for (int index = 0; index < excelFirstRow.Count; index++)
                            {
                                if (excelFirstRow[index].Contains(like))
                                {
                                    columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = index });
                                    break;
                                }
                            }
                        }
                        else if (excelIndex > -1)
                        {
                            columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = excelIndex });
                        }
                        else
                        {
                            columns.Add(new EntityColumn { ColumnName = property.Name, ExcelIndex = propertyIndex });
                        }
                    }
                    propertyIndex++;
                }
                #endregion

                // 遍历数据行
                while (rows.MoveNext())
                {
                    var row = (IRow)rows.Current;
                    var entity = new T();
                    PropertyInfo[] items = entity.GetType().GetProperties();
                    foreach (PropertyInfo property in items)
                    {
                        EntityColumn column = columns.FirstOrDefault(t => t.ColumnName == property.Name);
                        if (column != null)
                        {
                            ICell cell = row.GetCell(column.ExcelIndex);
                            if (cell == null)
                            //if (column.ExcelIndex - row.FirstCellNum >= row.Cells.Count || column.ExcelIndex - row.FirstCellNum < 0)
                            {
                                continue;
                            }

                            Object val = cell.ToString();

                            #region 数据类型转换
                            if (property.PropertyType == typeof(DateTime))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = cell.DateCellValue;
                                }
                                else
                                {
                                    DateTime d;
                                    DateTime.TryParse(val.ToString(), out d);
                                    val = d;
                                }
                            }
                            else if (property.PropertyType == typeof(string))
                            {
                                val = val.ToString().Trim();
                            }
                            if (property.PropertyType == typeof(Int32))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = (int)Math.Round(cell.NumericCellValue);
                                }
                                Int32 d;
                                Int32.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(UInt32))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = Math.Round(cell.NumericCellValue);
                                }
                                UInt32 d;
                                UInt32.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(Int16))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = Math.Round(cell.NumericCellValue);
                                }
                                Int16 d;
                                Int16.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(UInt16))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = Math.Round(cell.NumericCellValue);
                                }
                                UInt16 d;
                                UInt16.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(float))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = cell.NumericCellValue;
                                }
                                float d;
                                float.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(decimal))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = cell.NumericCellValue;
                                }
                                decimal d;
                                decimal.TryParse(val.ToString(), out d);
                                val = d;
                            }
                            else if (property.PropertyType == typeof(double))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = cell.NumericCellValue;
                                }
                                else
                                {
                                    double d;
                                    double.TryParse(val.ToString(), out d);
                                    val = d;
                                }
                            }
                            else if (property.PropertyType == typeof(byte))
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    val = (byte)Convert.ToInt32(cell.NumericCellValue);
                                }
                                else
                                {
                                    double d;
                                    double.TryParse(val.ToString(), out d);
                                    val = (byte)d;
                                }
                            }
                            #endregion

                            property.SetValue(entity, val, null);
                        }
                    }

                    entities.Add(entity);
                }

                // 返回结果
                return entities;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        private class EntityColumn
        {
            public string ColumnName { get; set; }

            public int ExcelIndex { get; set; }
        }
    }


    /// <summary>
    /// Excel列属性【添加标签对象】
    /// </summary>
    public class ExcelColumnAttribute : Attribute
    {
        public ExcelColumnAttribute()
        {
            ExcelIndex = -1;
        }

        public ExcelColumnAttribute(int excelIndex)
        {
            ExcelIndex = excelIndex;
        }

        public ExcelColumnAttribute(string displayNameLike)
        {
            DisplayNameLike = displayNameLike;
        }

        public int ExcelIndex { get; set; }

        public string DisplayNameLike { get; set; }
    }
}
